<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2020 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: https://thinkadmin.top
// +----------------------------------------------------------------------
// | 开源协议 ( https://mit-license.org )
// +----------------------------------------------------------------------
// | gitee 代码仓库：https://gitee.com/zoujingli/ThinkAdmin
// | github 代码仓库：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace app\index\controller;

use think\admin\Controller;
use think\admin\service\AdminService;
use think\admin\service\NodeService;
use think\facade\Cache;
use think\facade\Db;
use think\facade\Request;

/**
 * 会员分析
 * @package app\index\controller
 */
class Index extends Controller
{
    public function index()
    {
        $this->redirect(sysuri('admin/login/index'));
    }
    /**
     * 移动管理端
     */
    public function home()
    {

        $this->_form(Db::connect('zs')->table('member_meal'), 'home');
    }
    /**
     * 新会员数据统计
     */
    public function userSum()
    {
        //2021年新会员总数
        $nytime = date("Y-01-01");
        $StartTxDate0 = date("Y0101"); //当年
        $StartTxDate = "TO_DATE('$StartTxDate0', 'YYYY-MM-DD HH24:MI:SS')";

        $sqlCrm="SELECT distinct XF_VIPCODE FROM XF_VIP WHERE EB_CREATE_DATETIME>= $StartTxDate ";
        $sumCrm = Db::connect('crm')->query($sqlCrm);
        $sumCrm=count($sumCrm);

        //当月新增会员
        $StartTxDate0 = date("YM01"); //当月
        $StartTxDate = "TO_DATE('$StartTxDate0', 'YYYY-MM-DD HH24:MI:SS')";
        $mCrm="SELECT distinct XF_VIPCODE FROM XF_VIP WHERE EB_CREATE_DATETIME>= $StartTxDate ";
        $mSumCrm = Db::connect('crm')->query($mCrm);
        $mSumCrm=count($mSumCrm);

        //当日新增会员
        $dStartTxDate0 = date("Ymd"); //当日
        $dStartTxDate = "TO_DATE('$dStartTxDate0', 'YYYY-MM-DD HH24:MI:SS')";
        $dCrm="SELECT distinct XF_VIPCODE FROM XF_VIP WHERE EB_CREATE_DATETIME>= $dStartTxDate ";
        $dSumCrm = Db::connect('crm')->query($dCrm);
        $dSumCrm=count($dSumCrm);

        //1卡会员来源分析
        $user = Db::connect('zs')->table('member_meal')
            ->alias('a')
            ->join('zs.business b','b.id= a.bid')
            ->whereTime('a.create_at','>=',$nytime)
            ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
            ->group('a.bid')
            ->order('population', 'desc')
            ->select();
        //来源占比
        //244 总台

        $zld = $this->getSum('140,141,142','','');
        $zt = $this->getSum('244','','');
        $hdl = $this->getSum('292','','');
        $ms = $this->getNosum('0,140,141,142,244,272,292,99999999','','');
        $yylm = $this->yyNosum('','');
        $bh = $this->bhNoSum('0','','')-$zld-$zt;

        $gdp[] = ['name'=> '主力店','percent'=> $zld];
        $gdp[] = ['name'=> '中央收银组','percent'=> $zt];
        $gdp[] = ['name'=> '美食','percent'=> $ms];
        $gdp[] = ['name'=> '异业联盟','percent'=> $yylm];
        $gdp[] = ['name'=> '海底捞','percent'=> $hdl];
        $gdp[] = ['name'=> '百货','percent'=> $bh];

        $zb = $zld+$zt+$hdl+$ms+$yylm;
        $zldzb = round($zld/$zb*100,2).'%';
        $ztzb = round($zt/$zb*100,2).'%';
        $mszb = round($ms/$zb*100,2).'%';
        $yylmzb = round($yylm/$zb*100,2).'%';
        $hdlzb = round($hdl/$zb*100,2).'%';
        $bhzb = round($bh/$zb*100,2).'%';

        $gdpzb = ['主力店' => $zldzb,'中央收银组' => $ztzb,'美食' => $mszb,'异业联盟' => $yylmzb,'海底捞' => $hdlzb,'百货' => $bhzb];

        $data = [
            'sumCrm' => $sumCrm,
            'mSumCrm' => $mSumCrm,
            'dSumCrm' => $dSumCrm,
            'user' => $user,
            'gdp' => $gdp,
            'gdpzb' => $gdpzb
        ];

        return json_encode($data);
    }

    public function getTime($stime,$dtime){
        $stime = $stime ?: [date("Y-01-01")];
        $dtime = $dtime ?: [date("Y-12-31")];
        $stime = implode($stime).'00:00:00';
        $dtime = implode($dtime).'23:59:59';
        //var_dump($s);
        //return json_encode($s,$d);
        //1卡会员来源分析
        $user = Db::connect('zs')->table('member_meal')
            ->alias('a')
            ->join('zs.business b','b.id= a.bid')
            ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
            ->group('a.bid')
            ->order('population', 'desc')
            ->whereTime('a.create_at', 'between', [$stime, $dtime])
            ->select();

        //来源占比
        //244 总台

        $zld = $this->getSum('140,141,142',$stime,$dtime);
        $zt = $this->getSum('244',$stime,$dtime);
        $hdl = $this->getSum('292',$stime,$dtime);
        $ms = $this->getNosum('0,140,141,142,244,272,292,99999999',$stime,$dtime);
        $yylm = $this->yyNosum($stime,$dtime);
        $bh = $this->bhNoSum('0',$stime,$dtime)-$zld-$zt;

        $gdp[] = ['name'=> '主力店','percent'=> $zld];
        $gdp[] = ['name'=> '中央收银组','percent'=> $zt];
        $gdp[] = ['name'=> '美食','percent'=> $ms];
        $gdp[] = ['name'=> '异业联盟','percent'=> $yylm];
        $gdp[] = ['name'=> '海底捞','percent'=> $hdl];
        $gdp[] = ['name'=> '百货','percent'=> $bh];

        $zb = $zld+$zt+$hdl+$ms+$yylm;
        $zldzb = round($zld/$zb*100,2).'%';
        $ztzb = round($zt/$zb*100,2).'%';
        $mszb = round($ms/$zb*100,2).'%';
        $yylmzb = round($yylm/$zb*100,2).'%';
        $hdlzb = round($hdl/$zb*100,2).'%';
        $bhzb = round($bh/$zb*100,2).'%';

        $gdpzb = ['主力店' => $zldzb,'中央收银组' => $ztzb,'美食' => $mszb,'异业联盟' => $yylmzb,'海底捞' => $hdlzb,'百货' => $bhzb];

        $data = [
            'sumCrm' => 0,
            'mSumCrm' => 0,
            'dSumCrm' => 0,
            'user' => $user,
            'gdp' => $gdp,
            'gdpzb' => $gdpzb
        ];

        return json_encode($data);
    }
    /**
     * 包含ID的数据
     * @param $bid
     * @return float|int
     */
    public function getSum($bid,$stime,$dtime){
        if($stime && $dtime){
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->whereIn('a.bid',$bid)
                ->order('population', 'desc')
                ->whereTime('a.create_at', 'between', [$stime, $dtime])
                ->select()
                ->toArray();
        }else{
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->whereIn('a.bid',$bid)
                ->order('population', 'desc')
                ->select()
                ->toArray();
        }
        $zts=array_column($zt,'population');
        $ztsGP=array_sum($zts);

        return $ztsGP;
    }
    /**
     * 不包含ID的数据,美食商户
     * @param $bid
     * @return float|int
     */
    public function getNoSum($bid,$stime,$dtime){
        if($stime && $dtime){
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->whereNotIn('a.bid',$bid)
                ->where('b.btype',1)
                ->order('population', 'desc')
                ->whereTime('a.create_at', 'between', [$stime, $dtime])
                ->select()
                ->toArray();
        }else{
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->whereNotIn('a.bid',$bid)
                ->where('b.btype',1)
                ->order('population', 'desc')
                ->select()
                ->toArray();
        }

        $zts=array_column($zt,'population');
        $ztsGP=array_sum($zts);

        return $ztsGP;
    }
    /**
     * 不包含ID的数据,百货商户
     * @param $bid
     * @return float|int
     */
    public function bhNoSum($bid,$stime,$dtime){
        if($stime && $dtime){
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->whereNotIn('a.bid',$bid)
                ->where('b.btype',2)
                ->order('population', 'desc')
                ->whereTime('a.create_at', 'between', [$stime, $dtime])
                ->select()
                ->toArray();
        }else{
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->whereNotIn('a.bid',$bid)
                ->where('b.btype',2)
                ->order('population', 'desc')
                ->select()
                ->toArray();
        }

        $zts=array_column($zt,'population');
        $ztsGP=array_sum($zts);

        return $ztsGP;
    }
    /**
     * 不包含ID的数据,异业联盟
     * @param $bid
     * @return float|int
     */
    public function yyNoSum($stime,$dtime){
        if($stime && $dtime){
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->where('b.btype',3)
                ->order('population', 'desc')
                ->whereTime('a.create_at', 'between', [$stime, $dtime])
                ->select()
                ->toArray();
        }else{
            $zt = Db::connect('zs')->table('member_meal')
                ->alias('a')
                ->join('zs.business b','b.id= a.bid')
                ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
                ->group('a.bid')
                ->where('b.btype',3)
                ->order('population', 'desc')
                ->select()
                ->toArray();
        }

        $zts=array_column($zt,'population');
        $ztsGP=array_sum($zts);

        return $ztsGP;
    }
    /**
     * 不包含ID的数据
     * @param $bid
     * @return float|int
     */
    public function NoSum($bid){
        $zt = Db::connect('zs')->table('member_meal')
            ->alias('a')
            ->join('zs.business b','b.id= a.bid')
            ->field('a.bid,a.vipcode,b.brandname as country,count(a.id) as population')
            ->group('a.bid')
            ->whereNotIn('a.bid',$bid)
            ->order('population', 'desc')
            ->select()
            ->toArray();
        $zts=array_column($zt,'population');
        $ztsGP=array_sum($zts);

        return $ztsGP;
    }
    /**
     * 移动端分享奖励分析
     */
    public function reward()
    {

        $this->_form(Db::connect('zs')->table('member_meal'), 'reward');
    }
    /**
     * 移动端分享奖励数据
     * reward_log
     */
    public function rewardData()
    {
        $activityId = Request::param('activityId');
        if($activityId){
            $where['a.business_activity_id'] = $activityId;
            $wheres['business_activity_id'] = $activityId;
        }else{
            $where = '';
            $wheres = '';
        }
        $jb = Db::connect('zs')->table('reward_log')
            ->alias('a')
            ->join('business_activity b','b.id= a.business_activity_id')
            ->field('a.type,a.vipcode,a.business_activity_id,b.title,sum(a.gold) as population')
            ->group('a.vipcode')
            ->where($where)
            ->order('population', 'desc')
            ->select()
            ->toArray();
        //活动
        $activity = Db::connect('zs')->table('reward_log')
            ->alias('a')
            ->join('business_activity b','b.id= a.business_activity_id')
            ->field('a.business_activity_id as value,b.title')
            ->group('b.title')
            ->where($where)
            ->order('b.id', 'desc')
            ->select()
            ->toArray();
        //转发总数
        $zf = Db::connect('zs')->table('reward_log')
            ->where('type', 1) //1-转发，2-访问，3-下载，4-新会员，5-种草
            ->where($wheres)
            ->count();
        //访问总数
        $fw = Db::connect('zs')->table('reward_log')
            ->where('type', 2) //1-转发，2-访问，3-下载，4-新会员，5-种草
            ->where($wheres)
            ->count();
        //下载总数
        $xz = Db::connect('zs')->table('reward_log')
            ->where('type', 3) //1-转发，2-访问，3-下载，4-新会员，5-种草
            ->where($wheres)
            ->count();
        //新会员总数
        $newUser = Db::connect('zs')->table('reward_log')
            ->where('type', 4) //1-转发，2-访问，3-下载，4-新会员，5-种草
            ->where($wheres)
            ->count();
        //总数金币
        $zjb = Db::connect('zs')->table('reward_log')
            ->where($wheres)
            ->sum('gold');
        $data = [
            'jb' => $jb,
            'zf' => $zf,
            'fw'=> $fw,
            'xz'=> $xz,
            'newUser'=> $newUser,
            'zjb'=> $zjb,
            'activity'=> $activity
        ];

        return json_encode($data);
    }
    /**
     * 移动管理端
     */
    public function consumption_number()
    {

        $this->_form(Db::connect('zs')->table('member_meal'), 'consumption_number');
    }

    /**
     * 1卡消费分析
     * @auth true
     * @menu true
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function pconsumption_number(){
        $this->title = '本年度1卡消费分析';
//        $consumption = Db::connect('zs')->table('meal_details_store')
//            ->alias("m")
//            ->join('business b', 'b.id = m.businessid')
//            ->where(['m.pay_status'=>0])
//            ->where(['b.btype'=>3])
//            ->field("b.brandname,sum(case  when m.pay_status = 0 then 1 else 0 end) as 'allSum',ROUND(sum(m.real_amount),2) as 'amount',b.id as 'bid',b.btype")
//            ->order('m.id desc')
//            ->group('m.businessid')
//            ->select()
//            ->toArray();
//        foreach ($consumption as $key=>$vo)
//        {
//            $consumption[$key]['bcount'] = Db::connect('zs')->table('member_meal')->where('bid',$vo['bid'])->count();
//            $consumption[$key]['camount'] = Db::connect('zs')->table('member_coupon_order')->where('status',0)->where('bid',$vo['bid'])->sum('amount');
//            $consumption[$key]['ccount'] =  Db::connect('zs')->table('member_coupon_order')->where('status',0)->where('bid',$vo['bid'])->count();
//        }
//        $this->assign('consumption',$consumption);
        $this->_form(Db::connect('zs')->table('member_meal'), 'pconsumption_number');
    }

    public function get_consumption(){
        $nytime = date('Y-01-01');
        $meal = Db::connect('zs')->table('meal')
            ->where('status','<',4)
            ->column('mobile');
        $vipcode = Db::connect('zs')->table('member_meal')
            ->where('status',1)
            ->whereIn('mobile',$meal)
            ->column('vipcode');

        $consumption = Db::connect('zs')->table('meal_details_store')
            ->alias("m")
            ->join('business b', 'b.id = m.businessid')
            ->where(['m.pay_status'=>0])
            ->whereNotIn('m.vipcode',$vipcode)
            ->whereTime('m.create_at','>=',$nytime)
            ->field("b.brandname,sum(case  when m.pay_status = 0 then 1 else 0 end) as 'allSum',ROUND(sum(m.real_amount),2) as 'amount',b.id as 'bid',b.btype")
            ->order('m.id desc')
            ->group('m.businessid')
            ->select()
            ->toArray();
        $time=array();
        $time[0]['0'] = date('Y-m-d 00:00:00',time());
        $time[0]['1'] = date('Y-m-d 23:59:59',strtotime("+1 day"));
        $time[1]['0'] = date('Y-m-01 00:00:00',time());
        $time[1]['1'] = date('Y-m-01 00:00:00',strtotime("+1 month"));
        $time[2]['0'] = date('Y-01-01 00:00:00',time());
        $time[2]['1'] = date('Y-01-01 00:00:00',strtotime("+1 year"));
        $acount=array();
        foreach ($time as $key =>$vo){
            $acount[$key] = Db::connect('zs')->table('meal_details_store')
                ->where(['pay_status'=>0])
                ->whereNotIn('vipcode',$vipcode)
                ->where('create_at','between time',[$vo[0],$vo[1]])
                ->field("sum(case  when pay_status = 0 then 1 else 0 end) as 'allSum'")
                ->order('id desc')
                ->find();
        }

        $data = Db::connect('zs')->table('member_meal')
            ->group('bid')
            ->whereTime('create_at','>=',$nytime)
            ->order('id desc')
            ->column('count(bid)','bid');

        $bsale = Db::connect('zs')->table('member_meal')
            ->whereTime('create_at','>=',$nytime)
            ->group('bid')
            ->order('id desc')
            ->column('vipcode,bid');
        $bsarr = array();
        foreach ($bsale as $vo)
        {
            $bsarr[$vo['bid']][]=$vo['vipcode'];
        }
        foreach ($bsarr as &$vb)
        {
            $vb['account'] = Db::connect('zs')->table('meal_details_store')
                ->whereIn('vipcode',$vb)
                ->where(['pay_status'=>0])
                ->group('vipcode')
                ->count();
        }
        foreach ($consumption as $ki =>$vi){
            $consumption[$ki]['bcount']=$data[$vi['bid']]  ?? 0;//新会员数
            $consumption[$ki]['scount']=$bsarr[$vi['bid']]['account']  ?? 0;//有消费的新会员数
        }

        $acount['user']=$consumption;
        return json_encode($acount);
    }

    public function get_timeconsumption($stime,$dtime,$minmoney,$btype){

        if (empty($stime)&&empty($dtime))
        {
            $stime = date('Y-01-01',time());
            $dtime = date('Y-12-31',time());
        }
        if (in_array($btype,['非百货','百货','异业联盟'])){
            $arr = array('非百货'=>1,'百货'=>2,'异业联盟'=>3);
            $btype = $arr[$btype];
        }
        if (strstr($stime," - ")){
            $time = explode(" - ",$stime);
            $stime =$time[0];
            $dtime = $time[1];
        }
        if (!$minmoney){
            $minmoney=0;
        }
        $meal = Db::connect('zs')->table('meal')
            ->where('status','<',4)
            ->column('mobile');
        $vipcode = Db::connect('zs')->table('member_meal')
            ->where('status',1)
            ->whereIn('mobile',$meal)
            ->column('vipcode');

        $consumption = Db::connect('zs')->table('meal_details_store')
            ->alias("m")
            ->join('business b', 'b.id = m.businessid')
            ->where(['m.pay_status'=>0])
            ->where('amount','>',$minmoney)
            ->whereNotIn('m.vipcode',$vipcode);
        if ($btype){
            $consumption=$consumption->where('b.btype',$btype);
        }
        if ($stime){
            $consumption=$consumption->where('m.create_at','> time',$stime);
        }
        if ($dtime){
            $dtime = $dtime.' 23:59:59';
            $consumption=$consumption->where('m.create_at','< time',$dtime);
        }
        $consumption=$consumption->field("b.brandname,sum(case  when m.pay_status = 0 then 1 else 0 end) as 'allSum',ROUND(sum(m.real_amount),2) as 'amount',b.id as 'bid',b.btype")
            ->order('m.id desc')
            ->group('m.businessid')
            ->select()
            ->toArray();

        $data = Db::connect('zs')->table('member_meal')
            ->alias("m")
            ->join('business b', 'b.id = m.bid');
        if ($btype){
            $data=$data->where('b.btype',$btype);
        }
        if ($stime){
            $data=$data->where('m.create_at','> time',$stime);
        }
        if ($dtime){
            $data=$data->where('m.create_at','< time',$dtime);
        }
        $data = $data->group('m.bid')
            ->order('m.id desc')
            ->column('count(bid)','m.bid');

        $bsale = Db::connect('zs')->table('member_meal')
            ->whereBetweenTime('create_at', $stime, $dtime)
            ->group('bid')
            ->order('id desc')
            ->column('vipcode,bid');
        $bsarr = array();
        foreach ($bsale as $vo)
        {
            $bsarr[$vo['bid']][]=$vo['vipcode'];
        }
        foreach ($bsarr as &$vb)
        {
            $vb['account'] = Db::connect('zs')->table('meal_details_store')
                ->whereIn('vipcode',$vb)
                ->where(['pay_status'=>0])
                ->group('vipcode')
                ->count();
        }
        foreach ($consumption as $ki =>$vi){
            $consumption[$ki]['bcount']=$data[$vi['bid']]  ?? 0;//新会员数
            $consumption[$ki]['scount']=$bsarr[$vi['bid']]['account'] ?? 0;//有消费的新会员数
        }
        return json_encode($consumption);
    }

    public function dealt(){

        $this->_form(Db::connect('zs')->table('member_meal'), 'dealt');
    }

    public function meal_sales()
    {

        $this->_form(Db::connect('zs')->table('member_meal'), 'meal_sales');
    }

    public function getmeals()
    {
        $result = array();
        $arr = array();

        $arr['负一楼'] = array('1','7','9','10','13','15','17','19','22','25','26','28','30','31','34','52','65','71','72','74','77','78','79','80','81','83','85','86','87','89','90','243','255','256','260','261','262','276','282','283','285','294','305','323','338','349','350','351','358','359','360','362','367','377','390','1807','1808','1818','1819','1820');
        $arr['一楼'] = array('288','1811','1856');
        $arr['六楼'] = array('29','69','94','246','248','292','301','322','361','1833');
        $arr['联营'] = array('279','283','285','288','1856');
        $bid = array_merge($arr['负一楼'],$arr['一楼'],$arr['六楼'],$arr['联营']);

        $dtime = Request::param('dtime');//根据时间查询
        if ($dtime)
        {
            $tday = $dtime;
            $today = $dtime;
            $yesterday =  date("Y-m-d",strtotime("-1 day",strtotime($today)));
            $rowmonth = $dtime;
        }else{
            $today = date("Y-m-d");
            $yesterday =  date("Y-m-d",strtotime("-1 day",strtotime($today)));//昨天
            $rowmonth = date("Y-m-01",strtotime("+1 month",strtotime($today)));//截至查询时间
            $tday = $yesterday;
        }
        $tyear = date("Y-01-01",strtotime($today));//今年年初
        $last1 = date("Y",strtotime('-1 year',strtotime($today)));//去年年初

        $tmonth = date("Y-m-01",strtotime($today));//本月一号
        $tmonth1 = date("Y-m-01",strtotime("-1 month",strtotime($today)));//上一个月
        $tmonth2 = date("Y-m-01",strtotime("-2 month",strtotime($today)));//上二个月

        $lmonth0 = date("Y-m-d",strtotime("-11 months",strtotime($tmonth)));//去年下月
        $lmonth = date("Y-m-d",strtotime("-1 years",strtotime($tmonth)));//去年本月
        $lmonth1 = date("Y-m-d",strtotime("-1 years",strtotime($tmonth1)));//去年上一个月
        $lmonth2 = date("Y-m-d",strtotime("-1 years",strtotime($tmonth2)));//去年上二个月

        //商户当日销售排行,当天查询用昨天数据，日期查询日期当天
        $result['btoday'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereDay('birthday',$tday)->group('businessid')->order('l_amount desc')->column('businessid,SUM(real_amount) as l_amount');

        //商户当月销售 排行——负一楼，一楼，六楼，联营商户
        $result['fonemonth'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['负一楼'])->group('businessid')->order('l_amount desc')->column('businessid,SUM(real_amount) as l_amount');
        $result['onemonth'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['一楼'])->group('businessid')->order('l_amount desc')->column('businessid,SUM(real_amount) as l_amount');
        $result['sixmonth'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['六楼'])->group('businessid')->order('l_amount desc')->column('businessid,SUM(real_amount) as l_amount');
        $result['lymonth'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['联营'])->group('businessid')->order('l_amount desc')->column('businessid,SUM(real_amount) as l_amount');
        //楼层昨日累计销售
        $result['boneyesday'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereday('birthday',$yesterday)->whereIn('businessid',$arr['负一楼'])->column('SUM(real_amount) as l_amount');
        $result['oneyesday'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereday('birthday',$yesterday)->whereIn('businessid',$arr['一楼'])->column('SUM(real_amount) as l_amount');
        $result['sixyesday'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereday('birthday',$yesterday)->whereIn('businessid',$arr['六楼'])->column('SUM(real_amount) as l_amount');
        $result['lyyesday'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereday('birthday',$yesterday)->whereIn('businessid',$arr['联营'])->column('SUM(real_amount) as l_amount');

        //楼层当月累计
        $result['fonemall'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['负一楼'])->column('SUM(real_amount) as l_amount');
        $result['onemall'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['一楼'])->column('SUM(real_amount) as l_amount');
        $result['sixmall'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['六楼'])->column('SUM(real_amount) as l_amount');
        $result['lymall'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['联营'])->column('SUM(real_amount) as l_amount');

        //楼层年度累计
        $result['foneyear'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=', $tyear)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['负一楼'])->column('SUM(real_amount) as l_amount');
        $result['oneyear'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->whereTime('birthday','<=',$rowmonth)->where('real_amount','>','0')
            ->whereTime('birthday','>=', $tyear)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['一楼'])->column('SUM(real_amount) as l_amount');
        $result['sixyear'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=', $tyear)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['六楼'])->column('SUM(real_amount) as l_amount');
        $result['lyyear'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=', $tyear)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['联营'])->column('SUM(real_amount) as l_amount');

        //昨天，今年，去年销售累计
        $result['yester'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereday('birthday',$yesterday)->column('SUM(real_amount) as l_amount');
        $result['toyear'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=', $tyear)->whereTime('birthday','<=',$rowmonth)->column('SUM(real_amount) as l_amount');
        $result['yesyear'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereYear('birthday', $last1)->column('SUM(real_amount) as lamount');

        $data['0'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$lmonth2)->whereTime('birthday','<',$lmonth0)->whereIn('businessid',$arr['负一楼'])->group('LEFT(birthday,7)')->order('time asc')->column('LEFT(birthday,7) as time,round(SUM(real_amount)*0.0001 ,2)as l_amount');
        $data['1'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$lmonth2)->whereTime('birthday','<',$lmonth0)->whereIn('businessid',$arr['一楼'])->group('LEFT(birthday,7)')->order('time asc')->column('LEFT(birthday,7) as time,round(SUM(real_amount)*0.0001 ,2)as l_amount');
        $data['2'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$lmonth2)->whereTime('birthday','<',$lmonth0)->whereIn('businessid',$arr['六楼'])->group('LEFT(birthday,7)')->order('time asc')->column('LEFT(birthday,7) as time,round(SUM(real_amount)*0.0001 ,2)as l_amount');

        $data['3'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth2)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['负一楼'])->group('LEFT(birthday,7)')->order('time asc')->column("LEFT(birthday,7) as time,round(SUM(real_amount)*0.0001 ,2)as l_amount");
        $data['4'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth2)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['一楼'])->group('LEFT(birthday,7)')->order('time asc')->column("LEFT(birthday,7) as time,round(SUM(real_amount)*0.0001 ,2)as l_amount");
        $data['5'] = Db::connect('zs')->table('member_sales_details')->where('pay_status',0)->where('real_amount','>','0')
            ->whereTime('birthday','>=',$tmonth2)->whereTime('birthday','<=',$rowmonth)->whereIn('businessid',$arr['六楼'])->group('LEFT(birthday,7)')->order('time asc')->column("LEFT(birthday,7) as time,round(SUM(real_amount)*0.0001 ,2)as l_amount");
        $arr = array();
        $nowyear = date("Y",strtotime($today)).'年';
        $yesteryear = date("Y",strtotime("-1 year",strtotime($today))).'年';
        $times = array($yesteryear.'B1',$yesteryear.'F1',$yesteryear.'F6',$nowyear.'B1',$nowyear.'F1',$nowyear.'F6');
        $months = array(
            substr(date('Y',strtotime("-14 month",strtotime($today))),-2).'.'.date('m',strtotime("-2 month",strtotime($today))).'-'.substr(date('Y',strtotime('-2 month',strtotime($today))),-2).'.'.date('m',strtotime("-2 month",strtotime($today))),
            '',
            substr(date('Y',strtotime("-13 month",strtotime($today))),-2).'.'.date('m',strtotime("-1 month",strtotime($today))).'-'.substr(date('Y',strtotime('-1 month',strtotime($today))),-2).'.'.date('m',strtotime("-1 month",strtotime($today))),
            '',
            substr(date('Y',strtotime("-12 month",strtotime($today))),-2).'.'.date('m',strtotime("-0 month",strtotime($today))).'-'.substr(date('Y',strtotime('-0 month',strtotime($today))),-2).'.'.date('m',strtotime("-0 month",strtotime($today)))
        );
        foreach ($data as $key => $vo)
        {

            if ($vo)
            {

                if ($key>2)
                {
                    $arr[$key]['stack'] ='Search Engine';
                }else{
                    $arr[$key]['stack'] ='AD';
                }
                $arr[$key]['name'] = $times[$key];
                $arr[$key]['label'] = array('show'=> true, 'position'=> 'inside');
                $arr[$key]['type'] ='bar';
                $darr = array_chunk(array_column($vo,'l_amount'),3)[0];
                $arr[$key]['data'] = [];
                $arr[$key]['data'][0] = $darr[0] ?? 0;
                $arr[$key]['data'][1] = 0;
                $arr[$key]['data'][2] = $darr[1] ?? 0;
                $arr[$key]['data'][3] = 0;
                $arr[$key]['data'][4] = $darr[2] ?? 0;
            }
        }
        $d0 = [$arr[0]['data'][0]+$arr[1]['data'][0]+$arr[2]['data'][0],'',$arr[0]['data'][2]+$arr[1]['data'][2]+$arr[2]['data'][2],'',$arr[0]['data'][4]+$arr[1]['data'][4]+$arr[2]['data'][4]];
        $err[0] = ['barGap'=>'0','type'=>'bar','data'=>$d0,'label'=>['show'=> true, 'position'=> 'top','textStyle'=>['color'=>'#999'],'formatter'=>['c']]];
        $err[0]['itemStyle']=['normal'=> ['color'=>'rgba(128, 128, 128, 0)']];
        $err[1] = $arr[0];
        $err[2] = $arr[1];
        $err[3] = $arr[2];

        $d1 = [$arr[3]['data'][0]+$arr[4]['data'][0]+$arr[5]['data'][0],'',$arr[3]['data'][2]+$arr[4]['data'][2]+$arr[5]['data'][2],'',$arr[3]['data'][4]+$arr[4]['data'][4]+$arr[5]['data'][4]];
        $err[4] = $arr[3];
        $err[5] = $arr[4];
        $err[6] = $arr[5];
        $err[7] = ['barGap'=>'0','type'=>'bar','data'=>$d1,' itemStyle'=>[['normal'=>"== color: 'rgba(128, 128, 128, 0)' == "]],'label'=>['show'=> true, 'position'=> 'top','textStyle'=>['color'=>'#999'],'formatter'=>['c']]];
        $err[7]['itemStyle']=['normal'=> ['color'=>'rgba(128, 128, 128, 0)']];

        $arr = $err;
        $arr = array_values($arr);
        //楼层本年和去年的最近三月销售
        $business = Db::connect('zs')->table('business')->column('brandname','id');
        $larr = array();
        $larr['arr'] = $arr;
        $larr['result'] = $result;
        $larr['months'] = $months;
        $larr['business'] = $business;

        return json_encode($larr);
    }

    /**
     * @login true
     */
    public function testCacheKey()
    {
        // $s = 'SystemAuthNode';
        // return Cache::store('file')->getCacheKey($s);

        $admin = AdminService::instance();
        $a = $admin->check('index');
        return json_encode(['a' => $a], JSON_UNESCAPED_UNICODE);

        $service = NodeService::instance();
        $methods = $service->getMethods();
        return json_encode($methods, JSON_UNESCAPED_UNICODE);
        [$real, $nodes] = [$service->fullnode($node), $service->getMethods()];
    }
}
